<?php

namespace App\Services\Exports\Sheets;

use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class HK591DataSheet implements WithTitle, WithHeadings, WithStyles, WithColumnWidths, FromArray, WithEvents
{
    private string $schemaConn = 'schema';

    private string $db = 'hk591_data';

    private array $schemaInfo;

    private array $tableInfo;

    private array $indexArr;

    private int $start = 1;

    private array $mergeCells;

    public function __construct()
    {
        $this->getSchemaInfo();
        $this->getColumns();
        $this->getIndex();
    }

    public function title(): string
    {
        return 'hk591_data';
    }

    public function headings(): array
    {
        return [
            '表名',
            'engine',
            '索引',
            '字段',
            '类型',
            'is_nullable',
            '字段注释',
            '表注释',
            '备注'
        ];
    }

    public function array(): array
    {
        $data = [];

        foreach ($this->tableInfo as $table) {
            $count = count($table);

            // 将所有需要合并的单元格写入到数据中
            $this->mergeCells[] = 'A' . $this->start + 1 . ':A' . ($this->start + $count);
            $this->mergeCells[] = 'B' . $this->start + 1 . ':B' . ($this->start + $count);
            $this->mergeCells[] = 'C' . $this->start + 1 . ':C' . ($this->start + $count);
            $this->mergeCells[] = 'H' . $this->start + 1 . ':H' . ($this->start + $count);

            $this->start = $this->start + $count;

            foreach ($table as $column) {
                $schema = $this->schemaInfo[$column['TABLE_NAME']];

                $indexStr = '';
                $index = $this->indexArr[$column['TABLE_NAME']] ?? [];
                foreach ($index as $k => $v) {
                    $indexStr .= $k . ':' . $v . "\n";
                }

                $data[] = [
                    $column['TABLE_NAME'],
                    $schema['ENGINE'],
                    $indexStr,
                    $column['COLUMN_NAME'],
                    $column['COLUMN_TYPE'],
                    $column['IS_NULLABLE'],
                    $column['COLUMN_COMMENT'],
                    $schema['TABLE_COMMENT'],
                    null
                ];
            }
        }

        return $data;
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                foreach ($this->mergeCells as $cell) {
                    $event->sheet->getDelegate()
                        ->mergeCells($cell)
                        ->getStyle($cell)
                        ->getAlignment()
                        ->setVertical(Alignment::VERTICAL_CENTER)       // 居中显示
                        ->setWrapText(true);                            // 换行
                }


            }
        ];
    }

    private function getSchemaInfo()
    {
        $result = DB::connection($this->schemaConn)
            ->table('TABLES')
            ->select('TABLE_NAME', 'ENGINE', 'TABLE_COMMENT')
            ->where('TABLE_SCHEMA', $this->db)
            ->get();

        $temp = [];

        foreach ($result as $v) {
            if (! isset($temp[$v->TABLE_NAME])) {
                $temp[$v->TABLE_NAME] = (array)$v;
            }
        }

        $this->schemaInfo = $temp;
    }

    private function getColumns()
    {
        $result = DB::connection($this->schemaConn)
            ->table('COLUMNS')
            ->select('TABLE_SCHEMA', 'TABLE_NAME', 'COLUMN_NAME', 'IS_NULLABLE', 'COLUMN_TYPE', 'COLUMN_COMMENT')
            ->where('TABLE_SCHEMA', $this->db)
            ->get();

        $temp = [];
        foreach ($result as $v) {
            if (! isset($temp[$v->TABLE_NAME])) {
                $temp[$v->TABLE_NAME] = [];
            }

            $temp[$v->TABLE_NAME][] = (array)$v;
        }

        $this->tableInfo = $temp;
    }

    private function getIndex()
    {
        $result = DB::connection($this->schemaConn)
            ->table('STATISTICS')
            ->select('TABLE_NAME', 'INDEX_NAME', 'COLUMN_NAME')
            ->where('INDEX_SCHEMA', $this->db)
            ->get();

        foreach ($result as $v) {
            if (! isset($this->indexArr[$v->TABLE_NAME])) {
                $this->indexArr[$v->TABLE_NAME] = [];
            }

            $this->indexArr[$v->TABLE_NAME][$v->COLUMN_NAME] = $v->INDEX_NAME;
        }
    }

    public function styles(Worksheet $sheet)
    {
        return [
            'A'  => ['font' => ['bold' => true, 'size' => 14]],
            'A1' => ['font' => ['bold' => true, 'size' => 14]],
            'B1' => ['font' => ['bold' => true, 'size' => 14]],
            'C1' => ['font' => ['bold' => true, 'size' => 14]],
            'D1' => ['font' => ['bold' => true, 'size' => 14]],
            'E1' => ['font' => ['bold' => true, 'size' => 14]],
            'F1' => ['font' => ['bold' => true, 'size' => 14]],
            'G1' => ['font' => ['bold' => true, 'size' => 14]],
            'H1' => ['font' => ['bold' => true, 'size' => 14]],
        ];
    }

    public function columnWidths(): array
    {
        return [
            'A' => 30,
            'B' => 15,
            'C' => 35,
            'D' => 20,
            'E' => 20,
            'F' => 15,
            'G' => 40,
            'H' => 30
        ];
    }
}
